package cn.com.dashihui.wx.service;

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.google.common.collect.Lists;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.wx.common.OrderCode;
import cn.com.dashihui.wx.dao.Category;
import cn.com.dashihui.wx.dao.Goods;

public class GoodsService {
	
	public Record getTagByCode(String code,int storeid){
		return Db.findFirst("SELECT * FROM t_bus_goods_tag WHERE code=? AND storeid=?",code,storeid);
	}
	
	/**
	 * 查询指定店铺的所有可用且可显示的商品标签，以及各标签下N条商品数据
	 * @param storeid
	 * @param max 要随标签显示的商品最大数量
	 */
	public List<Record> getAllTag(int storeid,int max){
		List<Record> list = Db.find("SELECT gt.tagName,gt.code tagCode,gt.orderNo,g.id,g.name,g.thumb,g.marketPrice,g.sellPrice,g.isSelf"
				+ " FROM t_bus_goods g"
				+ " INNER JOIN t_bus_goods_tag_rel gtr ON g.id=gtr.goodsid"
				+ " INNER JOIN t_bus_goods_tag gt ON gtr.tagid=gt.id AND gt.storeid=?"
				+ " WHERE g.state=1 AND g.storeid=? AND gt.enabled=1 AND gt.isShow=1"
				+ " ORDER BY gt.orderNo",storeid,storeid);
		if(list!=null){
			Map<String,Record> tags = new LinkedHashMap<String,Record>();
			for(Record row : list){
				String tagCode = row.get("tagCode");
				Record tag = tags.get(tagCode);
				List<Record> goodsList;
				if(tag==null){
					goodsList = new ArrayList<Record>();
					tags.put(tagCode, new Record()
							.set("tagName", row.get("tagName"))
							.set("tagCode", row.get("tagCode"))
							.set("list", goodsList));
				}else{
					goodsList = tag.get("list");
					//查询的商品数量如果大于最大值，则跳过
					if(goodsList.size()>=max){
						continue;
					}
				}
				goodsList.add(row);
			}
			return Lists.newArrayList(tags.values().iterator());
		}
		return null;
	}
	
	/**
	 * 查询指定标签的商品列表
	 * @param storeid 店铺ID
	 * @param tagCode 标签代码
	 * @param pageNum 页码
	 * @param pageSize 数量
	 */
	public Page<Record> findPageByTag(int storeid,String tagCode,int pageNum,int pageSize){
		String sqlSelect = "SELECT g.id,g.name,g.thumb,g.marketPrice,g.sellPrice,g.isSelf ";
		String sqlWhere = " FROM t_bus_goods g"
				+ " INNER JOIN t_bus_goods_tag_rel gtr ON g.id=gtr.goodsid"
				+ " INNER JOIN t_bus_goods_tag gt ON gtr.tagid=gt.id AND gt.storeid=?"
				+ " WHERE g.state=1 AND g.storeid=? AND gt.code=? AND gt.enabled=1 AND gt.isShow=1";
		return Db.paginate(pageNum, pageSize, sqlSelect, sqlWhere, storeid, storeid, tagCode);
	}
	
	/**
	 * 查询精品推荐的商品列表
	 * @param storeid 店铺ID
	 * @param pageNum 页码
	 * @param pageSize 数量
	 */
	public Page<Record> findPageByRecom(int storeid,int pageNum,int pageSize){
		String sqlSelect = "SELECT g.id,g.name,g.thumb,g.marketPrice,g.sellPrice,g.isSelf ";
		String sqlWhere = " FROM t_bus_goods g"
				+ " WHERE g.state=1 AND (g.storeid=? OR g.isSelf=1) AND g.type=2";
		return Db.paginate(pageNum, pageSize, sqlSelect, sqlWhere, storeid);
	}
	
	/**
	 * 查询商品列表
	 * @param storeid 店铺ID
	 * @param categoryonCode 一级分类代码
	 * @param categorytwCode 二级分类代码
	 * @param isSelf 是否筛选自营商品，1：是，其他：否
	 * @param pageNum 页码
	 * @param pageSize 数量
	 * @param orderBy 排序，0：默认排序，1：销量从高到低，2：价格从低到高，3：价格从高到低
	 */
	public Page<Record> findByPage(int storeid,String categoryonCode,String categorytwCode,int isSelf,int pageNum,int pageSize,int orderBy){
		String sqlSelect = "SELECT g.id,g.name,g.thumb,g.spec,g.marketPrice,g.sellPrice,g.urv,g.isSelf ";
		StringBuffer sqlFromSelect = new StringBuffer("FROM t_bus_goods g");
		StringBuffer sqlWhereSelect = new StringBuffer(" WHERE g.state=1");
		List<Object> params = new ArrayList<Object>();
		if(isSelf == 1){
			sqlWhereSelect.append(" AND g.isSelf=1");
		}else{
			sqlWhereSelect.append(" AND (g.storeid=? OR g.isSelf=1)");
			params.add(storeid);
		}
		//判断如果分类不为空，则添加条件
		if(!StrKit.isBlank(categoryonCode)){
			sqlFromSelect.append(" INNER JOIN t_dict_category c1 ON g.categoryonid=c1.categoryId");
			sqlWhereSelect.append(" AND c1.categoryNum=?");
			params.add(categoryonCode);
		}
		if(!StrKit.isBlank(categorytwCode)){
			sqlFromSelect.append(" INNER JOIN t_dict_category c2 ON g.categorytwid=c2.categoryId");
			sqlWhereSelect.append(" AND c2.categoryNum=?");
			params.add(categorytwCode);
		}
		//根据排序要求，作不同处理
		if(orderBy==0){
		}else if(orderBy==1){
			sqlSelect = sqlSelect + ",(SELECT COUNT(*) FROM t_bus_order bo INNER JOIN t_bus_order_list bol ON bo.orderNum=bol.orderNum WHERE bol.goodsid=g.id AND bo.orderState="+OrderCode.OrderState.FINISH+") saleCount ";
			sqlWhereSelect.append(" ORDER BY saleCount DESC");
		}else if(orderBy==2){
			sqlWhereSelect.append(" ORDER BY g.sellPrice ASC");
		}else if(orderBy==3){
			sqlWhereSelect.append(" ORDER BY g.sellPrice DESC");
		}
		return Db.paginate(pageNum, pageSize, sqlSelect, sqlFromSelect.append(sqlWhereSelect).toString(), params.toArray());
	}
	
	/**
	 * 查询商品信息
	 * @param storeid 店铺ID
	 * @param goodsid 商品ID
	 */
	public Goods findDetail(int storeid,int goodsid){
		String sql = "SELECT g.id,g.storeid,g.name,g.spec,g.shortInfo,g.marketPrice,g.sellPrice,g.thumb,g.state,g.type,g.urv,g.isSelf,g.isRebate,g.percent1,g.percent2,g.percent3,g.percent4,g.percent5,g.createDate,"
				+ "(CASE WHEN g.describe IS NULL OR length(g.describe)<10 THEN 0 ELSE 1 END) hasDescribe,"
				+ "0 isCollected,"
				+ "(SELECT COUNT(*) FROM t_bus_order bo INNER JOIN t_bus_order_list bol ON bo.orderNum=bol.orderNum WHERE bol.goodsid=? AND bo.orderState=?) saleCount,"
				+ "(SELECT COUNT(*) FROM t_bus_user_collection buc WHERE buc.goodsid=? AND buc.isCancel=0) collectedCount "
				+ "FROM t_bus_goods g WHERE g.state=1 AND g.id=? AND (g.storeid=? OR g.isSelf=1)";
		return Goods.me().findFirst(sql,goodsid,OrderCode.OrderState.FINISH,goodsid,goodsid,storeid);
	}
	
	/**
	 * 查询商品信息，并且查询该商品是否被当前登录用户收藏
	 * @param userid 当前登录用户ID
	 * @param storeid 店铺ID
	 * @param goodsid 商品ID
	 */
	public Goods findDetail(int userid,int storeid,int goodsid){
		String sql = "SELECT g.id,g.storeid,g.name,g.spec,g.shortInfo,g.marketPrice,g.sellPrice,g.thumb,g.state,g.type,g.urv,g.isSelf,g.isRebate,g.percent1,g.percent2,g.percent3,g.percent4,g.percent5,g.createDate,"
				+ "(CASE WHEN g.describe IS NULL OR length(g.describe)<10 THEN 0 ELSE 1 END) hasDescribe,"
				+ "(SELECT COUNT(*) FROM t_bus_user_collection buc WHERE buc.userid=? AND buc.goodsid=g.id AND buc.isCancel=0)!=0 isCollected,"
				+ "(SELECT COUNT(*) FROM t_bus_order bo INNER JOIN t_bus_order_list bol ON bo.orderNum=bol.orderNum WHERE bol.goodsid=? AND bo.orderState=?) saleCount,"
				+ "(SELECT COUNT(*) FROM t_bus_user_collection buc WHERE buc.goodsid=? AND buc.isCancel=0) collectedCount "
				+ "FROM t_bus_goods g WHERE g.state=1 AND g.id=? AND (g.storeid=? OR g.isSelf=1)";
		return Goods.me().findFirst(sql,userid,goodsid,OrderCode.OrderState.FINISH,goodsid,goodsid,storeid);
	}
	
	/**
	 * 查询商品图片集
	 * @param goodsid 商品ID
	 */
	public List<Record> findImages(int goodsid){
		return Db.find("SELECT thumb FROM t_bus_goods_images sgi WHERE goodsid=? ORDER BY orderNo",goodsid);
	}
	
	/**
	 * 查询商品信息描述
	 * @param storeid 店铺ID
	 * @param goodsid 商品ID
	 */
	public Goods findDescribe(int storeid,int goodsid){
		return Goods.me().findFirst("SELECT name,`describe` FROM t_bus_goods WHERE id=? AND storeid=?",goodsid,storeid);
	}
	
	/**
	 * 查找出所有产品分类，及各分类下商品数量
	 */
	public List<Category> findAllCategory(int storeid,int isSelf){
		String sql = "";
		if(isSelf == 1){
			sql = "SELECT r.categoryId,r.categoryType,r.categoryFatherId,r.categoryNum code,r.categoryName name,"
					+ "r.categoryNo,gtotal.categoryId,gtotal.total goodscount "
					+ " FROM t_dict_category r INNER JOIN"
					+ " (SELECT * FROM"
					+ " (SELECT g.categoryonid categoryId,count(*) total FROM t_bus_goods g WHERE g.isSelf=1 AND g.state=1 GROUP BY g.categoryonid UNION"
					+ " SELECT g.categorytwid categoryId,count(*) total FROM t_bus_goods g WHERE g.isSelf=1 AND g.state=1 GROUP BY g.categorytwid "
//					+ " SELECT g.categorythid categoryId,count(*) total FROM t_bus_goods g WHERE g.storeid=? AND g.state=1 GROUP BY g.categorythid UNION"
//					+ " SELECT g.categoryfoid categoryId,count(*) total FROM t_bus_goods g WHERE g.storeid=? AND g.state=1 GROUP BY g.categoryfoid"
					+ ") gtotal) gtotal"
					+ " ON r.categoryId=gtotal.categoryId ORDER BY r.categoryNo ASC";
			return trim(Category.me().find(sql));
		} else {
			sql = "SELECT r.categoryId,r.categoryType,r.categoryFatherId,r.categoryNum code,r.categoryName name,"
					+ "r.categoryNo,gtotal.categoryId,gtotal.total goodscount "
					+ " FROM t_dict_category r INNER JOIN"
					+ " (SELECT * FROM"
					+ " (SELECT g.categoryonid categoryId,count(*) total FROM t_bus_goods g WHERE g.storeid=? AND g.state=1 GROUP BY g.categoryonid UNION"
					+ " SELECT g.categorytwid categoryId,count(*) total FROM t_bus_goods g WHERE g.storeid=? AND g.state=1 GROUP BY g.categorytwid "
//					+ " SELECT g.categorythid categoryId,count(*) total FROM t_bus_goods g WHERE g.storeid=? AND g.state=1 GROUP BY g.categorythid UNION"
//					+ " SELECT g.categoryfoid categoryId,count(*) total FROM t_bus_goods g WHERE g.storeid=? AND g.state=1 GROUP BY g.categoryfoid"
					+ ") gtotal) gtotal"
					+ " ON r.categoryId=gtotal.categoryId ORDER BY r.categoryNo ASC";
			return trim(Category.me().find(sql,storeid,storeid));
		}
		
	}
	/**
	 * 对分类进行等级分类
	 */
	private List<Category> trim(List<Category> CategoryList){
		if(CategoryList==null||CategoryList.size()<=0){
			return null;
		}
		List<Category> listTypeone=new ArrayList<Category>();
		List<Category> listTypetwo=new ArrayList<Category>();
		List<Category> listTypethree=new ArrayList<Category>();
		List<Category> listTypefour=new ArrayList<Category>();
		
		//进行分类
		for(Category category : CategoryList){
			if(category.getInt("categoryType").intValue()==1){
				listTypeone.add(category);
			}else if(category.getInt("categoryType").intValue()==2){
				listTypetwo.add(category);
			}else if(category.getInt("categoryType").intValue()==3){
				listTypethree.add(category);
			}else{
				listTypefour.add(category);
			}
		}
		//组合4-3
		if(listTypefour.size()!=0){
			for(Category c4 : listTypefour){
				for(Category c3 : listTypethree){
					if(c4.getInt("categoryFatherId").equals(c3.getInt("categoryId"))){
						c3.addChild(c4);
						break;
					}
				}
			}
		}
		//组合3-2
		if(listTypethree.size()!=0){
			for(Category c3 : listTypethree){
				for(Category c2 : listTypetwo){
					if(c3.getInt("categoryFatherId").equals(c2.getInt("categoryId"))){
						c2.addChild(c3);
						break;
					}
				}
			}
		}
		//组合2-1
		if(listTypetwo.size()!=0){
			for(Category c2 : listTypetwo){
				for(Category c1 : listTypeone){
					if(c2.getInt("categoryFatherId").equals(c1.getInt("categoryId"))){
						c1.addChild(c2);
						break;
					}
				}
			}
		}
		return listTypeone;
	}
}
